package Yangtz.Cs.DAO;

import Yangtz.Cs.Entity.BookBean;
import Yangtz.Cs.Utils.JDBCUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class BookDao {
    //用来对用户表进行增删改查
    //添加用户
    public boolean insert(BookBean book) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = null;
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            sql = "INSERT INTO TB_Book " +
                    "VALUES('"
                    + book.getBkID()
                    + "','"
                    + book.getBkCode()
                    + "','"
                    + book.getBkName()
                    + "','"
                    + book.getBkAuthor()
                    + "','"
                    + book.getBkPress()
                    + "','"
                    + book.getBkPressDate()
                    + "','"
                    + book.getBkISBN()
                    + "','"
                    + book.getBkLanguage()
                    + "','"
                    + book.getBkPages()
                    + "','"
                    + book.getBkPrice()
                    + "','"
                    + book.getBkDateIn()
                    + "','"
                    + book.getBkBrief()
                    + "','"
                    + "在馆"
                    + "','"
                    + book.getBkPic()
                    + "')";
            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return false;
    }

    // 查询所有的Book对象
    public ArrayList<BookBean> findAll() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<BookBean> list = new ArrayList<BookBean>();
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            String sql = "SELECT * FROM TB_Book";
            rs = stmt.executeQuery(sql);
            // 处理结果集
            while (rs.next()) {
                BookBean book = new BookBean();
                book.setBkID(rs.getString("bkID"));
                book.setBkCode(rs.getString("bkCode"));
                book.setBkName(rs.getString("bkName"));
                book.setBkAuthor(rs.getString("bkAuthor"));
                book.setBkPress(rs.getString("bkPress"));
                book.setBkPressDate(rs.getString("bkPressDate"));
                book.setBkISBN(rs.getString("bkISBN"));
                book.setBkLanguage(rs.getString("bkLanguage"));
                book.setBkPages(rs.getString("bkPages"));
                book.setBkPrice(rs.getString("bkPrice"));
                book.setBkDateIn(rs.getString("bkDataIn"));
                book.setBkBrief(rs.getString("bkBrief"));
                book.setBkStatus(rs.getString("bkStatus"));
                book.setBkPic(rs.getString("bkPic"));
                list.add(book);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return null;
    }

    // 根据id查找指定的book
    public BookBean find(String id) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            String sql = "SELECT * FROM TB_Book WHERE rdID=" + id;
            rs = stmt.executeQuery(sql);
            // 处理结果集
            while (rs.next()) {
                BookBean book = new BookBean();
                book.setBkID(rs.getString("bkID"));
                book.setBkCode(rs.getString("bkCode"));
                book.setBkName(rs.getString("bkName"));
                book.setBkAuthor(rs.getString("bkAuthor"));
                book.setBkPress(rs.getString("bkPress"));
                book.setBkPressDate(rs.getString("bkPressDate"));
                book.setBkISBN(rs.getString("bkISBN"));
                book.setBkLanguage(rs.getString("bkLanguage"));
                book.setBkPages(rs.getString("bkPages"));
                book.setBkPrice(rs.getString("bkPrice"));
                book.setBkDateIn(rs.getString("bkDataIn"));
                book.setBkBrief(rs.getString("bkBrief"));
                book.setBkStatus(rs.getString("bkStatus"));
                book.setBkPic(rs.getString("bkPic"));
                return book;
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return null;
    }

    // 删除书
    public boolean delete(String id) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            String sql = "DELETE FROM TB_book WHERE bkID=" + id;
            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return false;
    }

    // 修改用户
    public boolean update(BookBean book) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句

            String sql = "UPDATE TB_book set " +
                    "bkCode='" + book.getBkCode() +
                    "',bkName='" + book.getBkName() +
                    "',bkAuthor='" + book.getBkAuthor() +
                    "',bkPress='" + book.getBkPress() +
                    "',bkPressDate='" + book.getBkPressDate() +
                    "',bkISBN='" + book.getBkISBN() +
                    "',bkLanguage='" + book.getBkLanguage() +
                    "',bkPages='" + book.getBkPages() +
                    "',bkPrice='" + book.getBkPrice() +
                    "',bkDataIn='" + book.getBkDateIn() +
                    "',bkBrief='" + book.getBkBrief() +
                    "',bkStatus='" + book.getBkStatus() +
                    "',bkPic='" + book.getBkStatus() +
                    "' WHERE rdID=" + book.getBkID();
            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return false;
    }

    public List<BookBean> searchBook(String string){
        List<BookBean> bookBeanList=new ArrayList<>();

        List<BookBean> books=findAll();
        System.out.println("Books"+books.size());
        for (int i = 0; i < books.size(); i++) {
            if(books.get(i).getBkName().toLowerCase().contains(string.toLowerCase())){
                bookBeanList.add(books.get(i));
            }
        }
        return bookBeanList;
    }

    public boolean modifyBook(BookBean book){

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句

            String sql = "UPDATE TB_book set " +
                    "bkName='" + book.getBkName() +
                    "',bkAuthor='" + book.getBkAuthor() +
                    "',bkPress='" + book.getBkPress() +
                    "',bkPrice='" + book.getBkPrice() +
                    "',bkBrief='" + book.getBkBrief() +
                    "' WHERE bkID=" + book.getBkID();

            System.out.println(sql);

            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return false;

    }

}


